package org.huel.backend.mapper;

import org.apache.ibatis.annotations.*;
import org.huel.backend.domain.entity.UserCourseMatrix;

import java.util.List;

@Mapper
public interface UserCourseMatrixMapper {

    /**
     * 查询指定学生的所有课程出勤率矩阵
     * @param studentId 学生ID
     * @return 学生的课程出勤率矩阵
     */
    @Select("SELECT * FROM user_course_matrix WHERE student_id = #{studentId}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "courseName", column = "course_name"),
            @Result(property = "attendanceRate", column = "attendance_rate"),
            @Result(property = "lastUpdated", column = "last_updated")
    })
    List<UserCourseMatrix> selectByStudentId(@Param("studentId") String studentId);

    /**
     * 查询指定课程的所有学生出勤率矩阵
     * @param courseName 课程名称
     * @return 课程的学生出勤率矩阵
     */
    @Select("SELECT * FROM user_course_matrix WHERE course_name = #{courseName}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "courseName", column = "course_name"),
            @Result(property = "attendanceRate", column = "attendance_rate"),
            @Result(property = "lastUpdated", column = "last_updated")
    })
    List<UserCourseMatrix> selectByCourseName(@Param("courseName") String courseName);

    /**
     * 查询出勤率低于指定阈值的记录
     * @param threshold 出勤率阈值
     * @return 低出勤率记录列表
     */
    @Select("SELECT * FROM user_course_matrix WHERE attendance_rate < #{threshold} ORDER BY attendance_rate")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "courseName", column = "course_name"),
            @Result(property = "attendanceRate", column = "attendance_rate"),
            @Result(property = "lastUpdated", column = "last_updated")
    })
    List<UserCourseMatrix> selectLowAttendanceRates(@Param("threshold") float threshold);

    /**
     * 查询学生在指定课程的出勤率矩阵记录
     * @param studentId 学生ID
     * @param courseName 课程名称
     * @return 学生在指定课程的出勤率矩阵记录
     */
    @Select("SELECT * FROM user_course_matrix WHERE student_id = #{studentId} AND course_name = #{courseName}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "courseName", column = "course_name"),
            @Result(property = "attendanceRate", column = "attendance_rate"),
            @Result(property = "lastUpdated", column = "last_updated")
    })
    UserCourseMatrix selectByStudentIdAndCourseName(@Param("studentId") String studentId, @Param("courseName") String courseName);

    /**
     * 插入新的用户课程矩阵记录
     * @param userCourseMatrix 用户课程矩阵实体
     * @return 影响的行数
     */
    @Insert("INSERT INTO user_course_matrix (student_id, course_name, attendance_rate) VALUES (#{studentId}, #{courseName}, #{attendanceRate})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertUserCourseMatrix(UserCourseMatrix userCourseMatrix);

    /**
     * 更新用户课程矩阵记录
     * @param userCourseMatrix 用户课程矩阵实体
     * @return 影响的行数
     */
    @Update("UPDATE user_course_matrix SET attendance_rate = #{attendanceRate} WHERE id = #{id}")
    int updateUserCourseMatrix(UserCourseMatrix userCourseMatrix);
}